1 using System;
2 using System.Collections.Generic;
3 using System.ComponentModel;
4 using System.Data;
5 using System.Drawing;
6 using System.Linq;
7 using System.Text;
8 using System.Windows.Forms;
9 using System.Data.SqlClient;
10 using Excel = Microsoft.Office.Interop.Excel;
11 using System.IO;
12 namespace WarehouseManagementSystem
13 {
14 public partial class frmProductsRecord2 : Form
15 {
16 SqlDataReader rdr = null;
17 SqlConnection con = null;
18 SqlCommand cmd = null;
19 ConnectionString cs = new ConnectionString();
20 public frmProductsRecord2()
21 {
22 InitializeComponent();
23 }
24 public void GetData()
25 {
26 try
27 {
28 con = new SqlConnection(cs.DBConn);
29 con.Open();
30 cmd = new SqlCommand("SELECT RTRIM(ProductID),RTRIM(ProductName),RTRIM(Category.ID),RTRIM(CategoryName),RTRIM(SubCategory.ID),RTRIM(SubCategoryName),RTRIM(Features),RTRIM(Price),Image from Product,Category,SubCategory where Product.CategoryID=Category.ID and Product.SubCategoryID=SubCategory.ID order by Productname", con);
31 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
32 dataGridView1.Rows.Clear();
33 while (rdr.Read() == true)
34 {
35 dataGridView1.Rows.Add(rdr[0],rdr[1],rdr[2],rdr[3],rdr[4],rdr[5],rdr[6],rdr[7],rdr[8]);
36 }
37 con.Close();
38 }
39 catch (Exception ex)
40 {
41 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
42 }
43 }
44 private void frmProductsRecord_Load(object sender, EventArgs e)
45 {
46 GetData();
47 }
48
49
50 private void Button3_Click(object sender, EventArgs e)
51 {
52 int rowsTotal = 0;
53 int colsTotal = 0;
54 int I = 0;
55 int j = 0;
56 int iC = 0;
57 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
58 Excel.Application xlApp = new Excel.Application();
59
60 try
61 {
62 Excel.Workbook excelBook = xlApp.Workbooks.Add();
63 Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelBook.Worksheets[1];
64 xlApp.Visible = true;
65
66 rowsTotal = dataGridView1.RowCount;
67 colsTotal = dataGridView1.Columns.Count - 1;
68 var _with1 = excelWorksheet;
69 _with1.Cells.Select();
70 _with1.Cells.Delete();
71 for (iC = 0; iC <= colsTotal; iC++)
72 {
73 _with1.Cells[1, iC + 1].Value = dataGridView1.Columns[iC].HeaderText;
74 }
75 for (I = 0; I <= rowsTotal - 1; I++)
76 {
77 for (j = 0; j <= colsTotal; j++)
78 {
79 _with1.Cells[I + 2, j + 1].value = dataGridView1.Rows[I].Cells[j].Value;
80 }
81 }
82 _with1.Rows["1:1"].Font.FontStyle = "Bold";
83 _with1.Rows["1:1"].Font.Size = 12;
84
85 _with1.Cells.Columns.AutoFit();
86 _with1.Cells.Select();
87 _with1.Cells.EntireColumn.AutoFit();
88 _with1.Cells[1, 1].Select();
89 }
90 catch (Exception ex)
91 {
92 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
93 }
94 finally
95 {
96 //RELEASE ALLOACTED RESOURCES
97 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
98 xlApp = null;
99 }
100 }
101
102
103 private void dataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
104 {
105 string strRowNumber = (e.RowIndex + 1).ToString();
106 SizeF size = e.Graphics.MeasureString(strRowNumber, this.Font);
107 if (dataGridView1.RowHeadersWidth < Convert.ToInt32((size.Width + 20)))
108 {
109 dataGridView1.RowHeadersWidth = Convert.ToInt32((size.Width + 20));
110 }
111 Brush b = SystemBrushes.ControlText;
112 e.Graphics.DrawString(strRowNumber, this.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2));
113
114 }
115
116 private void dataGridView1_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
117 {
118 try
119 {
120 DataGridViewRow dr = dataGridView1.SelectedRows[0];
121 this.Hide();
122 frmProduct frm = new frmProduct();
123 frm.Show();
124 frm.txtProductID.Text = dr.Cells[0].Value.ToString();
125 frm.txtProductName.Text = dr.Cells[1].Value.ToString();
126 frm.txtCategoryID.Text = dr.Cells[2].Value.ToString();
127 frm.cmbCategory.Text = dr.Cells[3].Value.ToString();
128 frm.txtSubCategoryID.Text = dr.Cells[4].Value.ToString();
129 frm.cmbSubCategory.Text = dr.Cells[5].Value.ToString();
130 frm.txtFeatures.Text = dr.Cells[6].Value.ToString();
131 frm.txtPrice.Text = dr.Cells[7].Value.ToString();
132 byte[] data = (byte[])dr.Cells[8].Value;
133 MemoryStream ms = new MemoryStream(data);
134 frm.pictureBox1.Image = Image.FromStream(ms);
135 frm.btnUpdate.Enabled = true;
136 frm.btnDelete.Enabled = true;
137 frm.btnSave.Enabled = false;
138 frm.txtProductName.Focus();
139 }
140
141 catch (Exception ex)
142 {
143 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
144 }
145 }
146
147 private void txtProductname_TextChanged(object sender, EventArgs e)
148 {
149 try
150 {
151 con = new SqlConnection(cs.DBConn);
152 con.Open();
153 cmd = new SqlCommand("SELECT RTRIM(ProductID),RTRIM(ProductName),RTRIM(Category.ID),RTRIM(CategoryName),RTRIM(SubCategory.ID),RTRIM(SubCategoryName),RTRIM(Features),RTRIM(Price),Image from Product,Category,SubCategory where Product.CategoryID=Category.ID and Product.SubCategoryID=SubCategory.ID and ProductName like '" + txtProductname.Text + "%' order by Productname", con);
154 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
155 dataGridView1.Rows.Clear();
156 while (rdr.Read() == true)
157 {
158 dataGridView1.Rows.Add(rdr[0], rdr[1], rdr[2], rdr[3], rdr[4], rdr[5], rdr[6], rdr[7],rdr[8]);
159 }
160 con.Close();
161 }
162 catch (Exception ex)
163 {
164 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
165 }
166 }
167
168 private void txtCategory_TextChanged(object sender, EventArgs e)
169 {
170 try
171 {
172 con = new SqlConnection(cs.DBConn);
173 con.Open();
174 cmd = new SqlCommand("SELECT RTRIM(ProductID),RTRIM(ProductName),RTRIM(Category.ID),RTRIM(CategoryName),RTRIM(SubCategory.ID),RTRIM(SubCategoryName),RTRIM(Features),RTRIM(Price),Image from Product,Category,SubCategory where Product.CategoryID=Category.ID and Product.SubCategoryID=SubCategory.ID and CategoryName like '" + txtCategory.Text + "%' order by Productname", con);
175 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
176 dataGridView1.Rows.Clear();
177 while (rdr.Read() == true)
178 {
179 dataGridView1.Rows.Add(rdr[0], rdr[1], rdr[2], rdr[3], rdr[4], rdr[5], rdr[6], rdr[7],rdr[8]);
180 }
181 con.Close();
182 }
183 catch (Exception ex)
184 {
185 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
186 }
187 }
188
189 private void txtSubCategory_TextChanged(object sender, EventArgs e)
190 {
191 try
192 {
193 con = new SqlConnection(cs.DBConn);
194 con.Open();
195 cmd = new SqlCommand("SELECT RTRIM(ProductID),RTRIM(ProductName),RTRIM(Category.ID),RTRIM(CategoryName),RTRIM(SubCategory.ID),RTRIM(SubCategoryName),RTRIM(Features),RTRIM(Price),Image from Product,Category,SubCategory where Product.CategoryID=Category.ID and Product.SubCategoryID=SubCategory.ID and SubCategoryName like '" + txtSubCategory.Text + "%' order by Productname", con);
196 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
197 dataGridView1.Rows.Clear();
198 while (rdr.Read() == true)
199 {
200 dataGridView1.Rows.Add(rdr[0], rdr[1], rdr[2], rdr[3], rdr[4], rdr[5], rdr[6], rdr[7],rdr[8]);
201 }
202 con.Close();
203 }
204 catch (Exception ex)
205 {
206 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
207 }
208 }
209
210
211 }
212 }
